<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL索引性能优化指南 | 技术小馆</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css">
    <link rel="stylesheet" href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Hiragino Sans GB", Simsun, sans-serif;
            line-height: 1.8;
            color: #333;
            background-color: #f9fafb;
        }
        h1, h2, h3, h4, h5 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
            color: #111827;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #4f46e5 0%, #7c3aed 100%);
        }
        .card-hover {
            transition: all 0.3s ease;
        }
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1), 0 10px 10px -5px rgba(0, 0, 0, 0.04);
        }
        .code-block {
            background-color: #1e293b;
            color: #e2e8f0;
            border-radius: 0.5rem;
            overflow-x: auto;
        }
        .first-letter::first-letter {
            float: left;
            font-size: 3.5em;
            line-height: 0.8;
            margin-right: 0.1em;
            color: #4f46e5;
            font-weight: bold;
        }
        .mermaid-tooltip {
            background-color: white !important;
            border: 1px solid #e5e7eb !important;
            border-radius: 0.375rem !important;
            padding: 0.5rem !important;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1), 0 2px 4px -1px rgba(0, 0, 0, 0.06) !important;
        }
    </style>
</head>
<body>
    <!-- Hero Section -->
    <section class="hero-gradient text-white py-20 px-4 sm:px-6 lg:px-8">
        <div class="max-w-5xl mx-auto text-center">
            <h1 class="text-4xl md:text-5xl font-bold mb-6">MySQL索引性能优化</h1>
            <p class="text-xl md:text-2xl opacity-90 mb-8 max-w-3xl mx-auto">深入解析索引使用场景与性能调优策略</p>
            <div class="flex justify-center space-x-4">
                <a href="#intro" class="px-6 py-3 bg-white text-indigo-700 font-medium rounded-lg hover:bg-opacity-90 transition">开始阅读</a>
                <a href="#visualization" class="px-6 py-3 border-2 border-white text-white font-medium rounded-lg hover:bg-white hover:bg-opacity-10 transition">查看可视化</a>
            </div>
        </div>
    </section>

    <!-- Main Content -->
    <div class="max-w-5xl mx-auto px-4 sm:px-6 lg:px-8 py-12">
        <!-- Introduction -->
        <section id="intro" class="mb-16">
            <div class="bg-white rounded-xl shadow-md overflow-hidden p-8 card-hover">
                <p class="first-letter text-lg text-gray-700 leading-relaxed">
                    在MySQL中，索引并非总是有效。虽然索引通常可以加快查询速度，但在某些情况下，索引可能无法带来预期的性能提升，甚至可能导致查询效率降低。理解索引的工作原理和使用场景，对于数据库性能优化至关重要。
                </p>
            </div>
        </section>

        <!-- Index Effectiveness Section -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-question-circle text-indigo-500 mr-3"></i>
                一、索引是否总是有效？
            </h2>

            <div class="grid md:grid-cols-2 gap-8">
                <!-- Card 1 -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-exclamation-triangle text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">查询使用不当</h3>
                        </div>
                        <ul class="space-y-3 text-gray-700">
                            <li class="flex items-start">
                                <i class="fas fa-chevron-right text-indigo-400 mt-1 mr-2 text-sm"></i>
                                <span><strong>范围查询</strong>：当使用范围查询时，MySQL可能无法充分利用索引</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-chevron-right text-indigo-400 mt-1 mr-2 text-sm"></i>
                                <span><strong>函数和表达式</strong>：在查询条件中使用函数或表达式会导致索引失效</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-chevron-right text-indigo-400 mt-1 mr-2 text-sm"></i>
                                <span><strong>隐式类型转换</strong>：类型转换可能导致索引失效</span>
                            </li>
                        </ul>
                    </div>
                </div>

                <!-- Card 2 -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-filter text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">索引选择性不高</h3>
                        </div>
                        <ul class="space-y-3 text-gray-700">
                            <li class="flex items-start">
                                <i class="fas fa-chevron-right text-indigo-400 mt-1 mr-2 text-sm"></i>
                                <span><strong>低选择性索引</strong>：如性别列只有两个值，全表扫描可能更快</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-chevron-right text-indigo-400 mt-1 mr-2 text-sm"></i>
                                <span><strong>索引覆盖不足</strong>：需要回表查找数据时会降低性能</span>
                            </li>
                        </ul>
                    </div>
                </div>

                <!-- Card 3 -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-tools text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">索引维护成本</h3>
                        </div>
                        <p class="text-gray-700">
                            对于频繁更新的表，索引的维护成本可能会很高，因为每次数据的插入、删除或更新都会导致索引的调整。如果索引不能显著提升查询性能，反而会增加写操作的开销。
                        </p>
                    </div>
                </div>

                <!-- Card 4 -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-brain text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">优化器决策</h3>
                        </div>
                        <p class="text-gray-700">
                            MySQL的查询优化器根据统计信息和查询代价来决定是否使用索引。有时优化器可能认为全表扫描比使用索引更快，尤其是在表较小或索引选择性不佳的情况下。
                        </p>
                    </div>
                </div>
            </div>
        </section>

        <!-- Visualization Section -->
        <section id="visualization" class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-project-diagram text-indigo-500 mr-3"></i>
                索引使用场景可视化
            </h2>

            <div class="bg-white rounded-xl shadow-md overflow-hidden p-6 card-hover">
                <div class="mermaid">
                    graph TD
                    A[索引使用决策] -->|高效场景| B[高选择性列]
                    A -->|高效场景| C[精确匹配查询]
                    A -->|高效场景| D[排序/分组操作]
                    A -->|低效场景| E[低选择性列]
                    A -->|低效场景| F[范围查询]
                    A -->|低效场景| G[函数/表达式]
                    A -->|低效场景| H[频繁更新表]
                    
                    B --> I[Cardinality高]
                    C --> J[WHERE col = value]
                    D --> K[ORDER BY/GROUP BY]
                    E --> L[如性别列]
                    F --> M[BETWEEN, >, <]
                    G --> N[UPPER(name) = 'JOHN']
                    H --> O[INSERT/UPDATE/DELETE开销大]
                </div>
            </div>
        </section>

        <!-- Checking Index Effectiveness Section -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-8 flex items-center">
                <i class="fas fa-search text-indigo-500 mr-3"></i>
                二、如何检查索引效果？
            </h2>

            <div class="space-y-8">
                <!-- EXPLAIN -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-terminal text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">EXPLAIN 语句</h3>
                        </div>
                        <p class="text-gray-700 mb-4">
                            <code>EXPLAIN</code> 是检查查询计划的主要工具，可以显示 MySQL 在执行查询时的决策。它会显示查询的执行计划，包括使用了哪些索引、表的连接顺序、查询的类型等信息。
                        </p>
                        <div class="code-block p-4 mb-4">
                            <code class="text-sm">
                                EXPLAIN SELECT * FROM users WHERE age = 30;
                            </code>
                        </div>
                        <div class="bg-gray-50 p-4 rounded-lg">
                            <h4 class="font-semibold text-gray-800 mb-2">结果分析：</h4>
                            <ul class="space-y-2 text-gray-700">
                                <li><strong>key</strong> 字段：显示使用的索引</li>
                                <li><strong>rows</strong> 字段：MySQL 预计扫描的行数</li>
                                <li><strong>type</strong> 字段：访问类型（ALL 效率最低，ref/eq_ref/const 效率高）</li>
                            </ul>
                        </div>
                    </div>
                </div>

                <!-- SHOW INDEX -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-table text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">SHOW INDEX 语句</h3>
                        </div>
                        <p class="text-gray-700 mb-4">
                            <code>SHOW INDEX</code> 可以查看表中索引的基本信息，包括索引的类型、是否唯一、索引列等。
                        </p>
                        <div class="code-block p-4 mb-4">
                            <code class="text-sm">
                                SHOW INDEX FROM users;
                            </code>
                        </div>
                        <div class="bg-gray-50 p-4 rounded-lg">
                            <h4 class="font-semibold text-gray-800 mb-2">结果分析：</h4>
                            <ul class="space-y-2 text-gray-700">
                                <li><strong>Cardinality</strong> 字段：索引的选择性，值越高选择性越好</li>
                                <li><strong>Non_unique</strong> 字段：是否为唯一索引</li>
                                <li><strong>Index_type</strong> 字段：索引类型（如BTREE）</li>
                            </ul>
                        </div>
                    </div>
                </div>

                <!-- Slow Query Log -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-clock text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">慢查询日志</h3>
                        </div>
                        <p class="text-gray-700 mb-4">
                            慢查询日志记录了执行时间超过设定阈值的查询。通过分析慢查询日志，可以发现哪些查询没有有效利用索引。
                        </p>
                        <div class="code-block p-4 mb-4">
                            <code class="text-sm">
                                # my.cnf配置示例
                                slow_query_log = 1
                                slow_query_log_file = /var/log/mysql/mysql-slow.log
                                long_query_time = 1
                            </code>
                        </div>
                        <div class="bg-gray-50 p-4 rounded-lg">
                            <h4 class="font-semibold text-gray-800 mb-2">分析方法：</h4>
                            <ul class="space-y-2 text-gray-700">
                                <li>使用 <code>mysqldumpslow</code> 工具分析日志</li>
                                <li>找出慢查询后使用 <code>EXPLAIN</code> 分析查询计划</li>
                                <li>检查是否因为索引使用不当导致查询变慢</li>
                            </ul>
                        </div>
                    </div>
                </div>

                <!-- Optimizer Hints -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <div class="bg-indigo-100 p-3 rounded-full mr-4">
                                <i class="fas fa-lightbulb text-indigo-600 text-xl"></i>
                            </div>
                            <h3 class="text-xl font-semibold">查询优化器提示</h3>
                        </div>
                        <p class="text-gray-700 mb-4">
                            有时可以通过查询优化器提示来强制 MySQL 使用特定的索引。但使用时需谨慎，确保强制使用的索引确实能够提高查询性能。
                        </p>
                        <div class="code-block p-4 mb-4">
                            <code class="text-sm">
                                SELECT * FROM users FORCE INDEX (idx_age) WHERE age = 30;
                            </code>
                        </div>
                        <div class="bg-gray-50 p-4 rounded-lg">
                            <h4 class="font-semibold text-gray-800 mb-2">注意事项：</h4>
                            <ul class="space-y-2 text-gray-700">
                                <li>仅在明确知道索引更优时使用</li>
                                <li>测试强制索引前后的性能差异</li>
                                <li>关注数据变化后索引的有效性</li>
                            </ul>
                        </div>
                    </div>
                </div>
            </div>
        </section>

        <!-- Summary Section -->
        <section class="mb-16">
            <div class="bg-indigo-50 rounded-xl p-8 border border-indigo-100">
                <h2 class="text-2xl font-bold text-indigo-800 mb-4 flex items-center">
                    <i class="fas fa-check-circle text-indigo-600 mr-3"></i>
                    关键总结
                </h2>
                <div class="grid md:grid-cols-2 gap-6">
                    <div>
                        <h3 class="font-semibold text-indigo-700 mb-2">索引有效场景</h3>
                        <ul class="space-y-2 text-indigo-900">
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-indigo-500 mt-1 mr-2"></i>
                                高选择性列上的精确查询
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-indigo-500 mt-1 mr-2"></i>
                                覆盖索引查询
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-indigo-500 mt-1 mr-2"></i>
                                排序和分组操作
                            </li>
                        </ul>
                    </div>
                    <div>
                        <h3 class="font-semibold text-indigo-700 mb-2">索引无效场景</h3>
                        <ul class="space-y-2 text-indigo-900">
                            <li class="flex items-start">
                                <i class="fas fa-times-circle text-red-500 mt-1 mr-2"></i>
                                低选择性列查询
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-times-circle text-red-500 mt-1 mr-2"></i>
                                使用函数/表达式的条件
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-times-circle text-red-500 mt-1 mr-2"></i>
                                频繁更新的表
                            </li>
                        </ul>
                    </div>
                </div>
            </div>
        </section>
    </div>

    <!-- Footer -->
    <footer class="bg-gray-900 text-gray-300 py-8 px-4">
        <div class="max-w-5xl mx-auto">
            <div class="flex flex-col md:flex-row justify-between items-center">
                <div class="mb-4 md:mb-0">
                    <h3 class="text-xl font-semibold text-white">技术小馆</h3>
                    <p class="mt-1">数据库性能优化专家</p>
                </div>
                <div>
                    <a href="http://www.yuque.com/jtostring" class="text-indigo-400 hover:text-indigo-300 transition flex items-center">
                        <i class="fas fa-external-link-alt mr-2"></i>
                        http://www.yuque.com/jtostring
                    </a>
                </div>
            </div>
            <div class="border-t border-gray-700 mt-6 pt-6 text-center text-sm">
                <p>© 2023 技术小馆. 版权所有.</p>
            </div>
        </div>
    </footer>

    <script>
        // Initialize Mermaid
        document.addEventListener('DOMContentLoaded', function() {
            mermaid.initialize({
                startOnLoad: true,
                theme: 'default',
                flowchart: {
                    useMaxWidth: false,
                    htmlLabels: true,
                    curve: 'basis'
                },
                themeCSS: `
                    .node rect {
                        fill: #e0e7ff;
                        stroke: #4f46e5;
                        stroke-width: 2px;
                        rx: 8;
                        ry: 8;
                    }
                    .node text {
                        fill: #1e293b;
                        font-family: 'Noto Sans SC', sans-serif;
                    }
                    .edgePath path {
                        stroke: #4f46e5;
                        stroke-width: 2px;
                    }
                `
            });
        });

        // Smooth scrolling for anchor links
        document.querySelectorAll('a[href^="#"]').forEach(anchor => {
            anchor.addEventListener('click', function (e) {
                e.preventDefault();
                document.querySelector(this.getAttribute('href')).scrollIntoView({
                    behavior: 'smooth'
                });
            });
        });
    </script>
</body>
</html>